package com.bl.student.dao.repository;

import com.bl.student.dao.model.custom.ArenaSearchEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

/**
 * Created by liangweikang on 18/4/28.
 */
public interface ArenaSearchRepository extends JpaRepository<ArenaSearchEntity, String> {

    @Query(value = "SELECT a.id,a.image,a.title,a.code,a.description,a.weight,a.type,a.status,a.creator_id,a.detail_id,a.start_time,a.end_time,a.create_time,a.update_time, d.collect as collect FROM ARENA a LEFT JOIN DETAIL d ON d.ID = a.DETAIL_ID WHERE a.START_TIME = ?1 AND a.TYPE = ?2 AND a.status = ?3 /*#pageable*/ ORDER BY d.collect DESC",
            countQuery = "SELECT COUNT(*) FROM ARENA a LEFT JOIN DETAIL d ON d.ID = a.DETAIL_ID " +
                    "WHERE a.start_time = ?1 AND a.type = ?2 AND a.status = ?3",
            nativeQuery = true)
    Page<ArenaSearchEntity> findByStatusAndStartTimeAndTypeOrderByCollectDesc(String startTime, String arenaType, String onlineStatus, Pageable pageable);

    @Query(value = "SELECT a.id,a.image,a.title,a.code,a.description,a.weight,a.type,a.status,a.creator_id,a.detail_id,a.start_time,a.end_time,a.create_time,a.update_time, d.collect as collect FROM ARENA a LEFT JOIN DETAIL d ON d.ID = a.DETAIL_ID WHERE AND a.TYPE = ?1 AND a.status = ?2 /*#pageable*/ ORDER BY d.collect DESC",
            countQuery = "SELECT COUNT(*) FROM ARENA a LEFT JOIN DETAIL d ON d.ID = a.DETAIL_ID " +
                    "WHERE a.TYPE = ?1 AND a.status = ?2",
            nativeQuery = true)
    Page<ArenaSearchEntity> findByStatusAndTypeOrderByCollecteDesc(String arenaType, String onlineStatus,Pageable pageable);

    @Query(value = "SELECT a.id,a.image,a.title,a.code,a.description,a.weight,a.type,a.status,a.creator_id,a.detail_id,a.start_time,a.end_time,a.create_time,a.update_time, d.collect as collect FROM ARENA a LEFT JOIN DETAIL d ON d.ID = a.DETAIL_ID WHERE a.TYPE = ?1 AND a.status = ?2 /*#pageable*/ ORDER BY d.collect DESC",
            countQuery = "SELECT COUNT(*) FROM ARENA a LEFT JOIN DETAIL d ON d.ID = a.DETAIL_ID " +
                    "WHERE a.type = ?1 AND a.status = ?2",
            nativeQuery = true)
    Page<ArenaSearchEntity> findByStatusAndTypeOrderByCollectDesc(String arenaType, String onlineStatus, Pageable pageable);

}